﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClientRegister
{
    public class ClassDataProvider
    {

        ConnectDB con = new ConnectDB();
        

        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;

        //Load danh sach tat ca cac lop hoc

        public int AddClass(string ID, string name, string idSub, string idTeach, string studyday, string room,
            string time, int fee, string start, string end, int accept)
        {

            string query = string.Format("insert into Classes(ID, NameClass,IDSubject,IDTearcher,StudyDay,"+
                "Room,Time,Fee,StartDate,EndDate,AcceptRegister,NewRecord,CreateDateTime) " +
                "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}',{10},{11},'{12}')", ID, name, idSub, idTeach, studyday, room, time,
                fee, DateTime.Parse(start), DateTime.Parse(end), accept, 1, DateTime.Now.ToString());
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        //Thay doi thong tin cua lop hoc
        public int ChangeInfoClass(string id, string name, string idTeach, string studyday, string room,
            string time, string fee, string start, string end, string accept, string note)
        {
            Service.RegisterService gettime = new Service.RegisterService();
            string currtime = gettime.currentTime();
            string query = string.Format("update Classes set NameClass = '{1}' ,IDTearcher = '{2}',StudyDay = '{3}'," +
                "Room = '{4}',Time = '{5}',Fee = {6},StartDate = '{7}',EndDate = '{8}',AcceptRegister = {9}, Note = '{10}',NewRecord = {11}, LastUpdate = '{12}' where ID = '{0}' ", id, name, idTeach, studyday, room, time,
                fee, DateTime.Parse(start), DateTime.Parse(end), accept, note, 2,DateTime.Parse(currtime));
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }
        //Convert Class record to xml string
        public string ConvertClassToXML(string idClass)
        {
            string query = string.Format("select * from Classes where ID = '{0}' for xml auto,elements", idClass);
            string xmlstring = "";
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }

        //Select ID cua record co cot new record = 1
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("select ID from Classes where NewRecord = 1");
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update Classes set NewRecord = 0 where NewRecord = 1");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 

        //Select ID cua record co cot new record = 2
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("select ID from Classes where NewRecord = 2");
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update Classes set NewRecord = 0 where NewRecord = 2");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 

        public DataSet loadAllClass()
        {
            string query = string.Format("Select ID,NameClass, IDSubject,StudyDay,Room,Time,Fee,StartDate,EndDate from Classes");
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        public DataSet loadAllInfoClass()
        {
            string query = string.Format("Select * from Classes");
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //load danh sach lop hoc cua mot khoa nao do
        public DataSet loadClass(string idCourse)
        {
            string query = string.Format("Select ID,NameClass,StudyDay,Room,Time,Fee,StartDate,EndDate from Classes,ClassInCourse where Classes.ID = ClassInCourse.IDClass And ClassInCourse.IDCourse ='{0}'", idCourse);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //load danh sach lop hoc cua hoc vien
        public DataSet loadClassOfStudent(string Id, string idCourse)
        {
            string query = string.Format("Select Classes.ID,NameClass,Classes.IDSubject,StudyDay,Room,Time,Fee,StartDate,EndDate " +
                "from Students,StudentInClass,Classes,ClassInCourse where "+
                "Students.ID = '{0}' and Students.ID = StudentInClass.IDStudent and StudentInClass.IDClass = Classes.ID and "+
                "Classes.ID = ClassInCourse.IDClass And " +
                "ClassInCourse.IDCourse = '{1}'", Id, idCourse);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Load tong hoc phi cac lop cua khoa hoc
        public int loadTotalFee(string idCourse)
        {
            string query = string.Format("Select sum(Fee) as TotalFee from Classes,ClassInCourse where Classes.ID = ClassInCourse.IDClass And ClassInCourse.IDCourse ='{0}'", idCourse);
            cnn = con.Connect();
            try
            {
                cnn = con.Connect();
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                int total = Convert.ToInt32(cmd.ExecuteScalar());
                return total;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }

        //Load thong tin cua mot lop hoc nao do
        public SqlDataReader ClassDetail(string idClass)
        {
            string query = string.Format("Select Fee,StudyDay,Time,StartDate,EndDate from Classes where ID ='{0}'", idClass);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            SqlDataReader sdr = cmd.ExecuteReader();
            return sdr;
        }

        public SqlDataReader SubjectInClass(string idClass)
        {
            string query = string.Format("Select IDSubject from Classes where ID ='{0}'", idClass);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            SqlDataReader sdr = cmd.ExecuteReader();
            return sdr;
        }

        //Load danh sach lop hoc co mon giong nhau
        public DataSet ListClassSameSubject(string Id, string IdSub)
        {
            string query = string.Format("Select ID,NameClass from Classes where IDSubject ='{1}' and ID not like '{0}'", Id, IdSub);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        public DataSet LoadAllClassOfStu(string IDStu)
        {
            string query = string.Format("Select Classes.ID,NameClass,Subjects.SubjectName,StudentInClass.Note,StudyDay,Room,Time,Fee,StartDate,EndDate "+
                "from Classes,Subjects,StudentInClass,Students where Students.ID = '{0}' and Students.ID = StudentInClass.IDStudent "+
                "and StudentInClass.IDClass = Classes.ID and Classes.IDSubject = Subjects.ID", IDStu);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
    }
}
